USE MASTER
GO
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='IMSDB')
DROP DATABASE IMSDB
GO
create database IMSDB;
go
use IMSDB;
go

/* USER */
create table tblUser(
	UserID int identity(1,1) primary key,
	UserName	varchar(100) not null,
	FirstName	varchar(100),
	LastName	varchar(100),
	Password	varchar(100),
	LastLoged	datetime,
	CreatedDate	datetime,
	CreatedBy	int,
	ModifyDate	datetime,
	ModifyBy	int,
	UserType int foreign key references tblCodeValue(CodeID)
);

alter table tblUser
add foreign key (CreatedBy) references tblUser(UserID);
go
alter table tblUser
add foreign key (ModifyBy) references tblUser(UserID);
go
ALTER TABLE [dbo].[tblUser]
ADD [IsActive] bit NULL 
GO
ALTER TABLE [dbo].[tblUser]
ADD [UserType] int NULL 
GO

/* Area */

create table tblCodeValueArea(
	AreaID int identity(1,1) primary key,
	Description varchar(100),
	CreatedBy int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifyBy int foreign key references tblUser(UserID),
	ModifyDate datetime
);

/*Code value*/
create table tblCode(
	CodeID int identity(1,1) primary key,
	AreaID int foreign key references tblCodeValueArea(AreaID),
	Description varchar(100),
	Value varchar(100),
	CreatedBy int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifyBy int foreign key references tblUser(UserID),
	ModifyDate datetime
);

ALTER TABLE [dbo].[tblUser] ADD FOREIGN KEY ([UserType]) REFERENCES [tblCode] ([CodeID]) ON DELETE NO ACTION ON UPDATE CASCADE
GO


/* ADDRESSS*/
create table tblCountry(
	CountryID	int identity(1,1) primary key,
	Country		varchar(100),
	CreatedBy	int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifiedBy	int foreign key references tblUser(UserID) default null,
	ModifyDate	datetime default null,
);
go

create table tblState(
	StateID	int identity(1,1) primary key,
	State	varchar(100),
	Zip	varchar(50),
	CountryID	int foreign key references tblCountry(CountryID),
	CreatedBy	int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifiedBy	int foreign key references tblUser(UserID) default null,
	ModifyDate	datetime default null,
);

go

create table tblCity(
	CityID	int identity(1,1) primary key,
	City	varchar(100),
	StateID	int foreign key references tblState(StateID),
	CreatedBy	int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifiedBy	int foreign key references tblUser(UserID) default null,
	ModifyDate	datetime default null,
);

go

create table tblTown(
	TownID	int identity(1,1) primary key,
	Town varchar(100),
	CityID	int foreign key references tblCity(CityID),
	CreatedBy	int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifiedBy	int foreign key references tblUser(UserID) default null,
	ModifyDate	datetime default null,
);
go

create table tblAddress(
	AddrID	int identity(1,1) primary key,
	StreetNo	varchar(100),
	StreetNo2	varchar(100),
	Town		varchar(100),
	City		varchar(100),
	State		varchar(100),
	Zip			varchar(100),
	Country		varchar(100),
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);
go

create table tblContact(
	ContactID	int identity(1,1) primary key,
	ContactName	varchar(100),
	Mobile		varchar(30),
	Phone		varchar(30),
	Phone2		varchar(30),
	Fax		varchar(30),
	Email	varchar(100),
	WebSite	varchar(100),		
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);
go

/* VENDOR*/
create table tblVender(
	VenderID	int identity(1,1) primary key,
	Name	varchar(100),
	Address int  foreign key references tblAddress(AddrID),
	Contact int  foreign key references tblContact(ContactID),
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

go

create table tblItem(
	ItemId	int identity(1,1) primary key,
	ItemCode	varchar(50),
	ItemName	varchar(100),
	Description	nvarchar(255),
	Unit	varchar(50),
	ItemsPerUnit	float,
	BuyingPrice		float,
	SellingPrice	float,
	Discount		float,
	TotalItems		float,
	GoodItems		float,
	BadItems		float,
	Vender  int  foreign key references tblVender(VenderID),
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID),
);
go

create table tblCustomer(
	CustomerID	int identity(1,1) primary key,
	Name	varchar(100),
	Address int  foreign key references tblAddress(AddrID),
	Contact int  foreign key references tblContact(ContactID),
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID),
);
go

create table tblEmployee(
	EmployeeID	int identity(1,1) primary key,
	Name	varchar(100),
	Address int  foreign key references tblAddress(AddrID),
	EmployeeType varchar(100),
	Contact int  foreign key references tblContact(ContactID),
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID),
)

go

create table tblVehicle(
	VehicleNo	varchar(30) primary key,
	VehicleType	varchar(50),
	FuelType	varchar(30),
	CreatedDate	datetime,
	MeterStart	float,
	KmPerLiter	float,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime,
	ModifyBy	int foreign key references tblUser(UserID),
);
go

create table tblBuyingInvoiceHeader(
	InvoiceID	int	primary key,
	VenderCode	varchar(50),
	Date		date,
	VenderID	int foreign key references tblVender(VenderID),
	TotalAmount	float,
	PaymentDueDate	date,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

go

create table tblBuyingInvoiceItems(
	InvoiceID	int foreign key references tblBuyingInvoiceHeader(InvoiceID),
	ItemID	int  foreign key references tblItem(ItemID),
	BuyingPrice	float,
	Discount float,
	Quantity float,
	SubTotal	float,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

create table tblBank(
	BankID	int primary key identity(1,1),
	Bank	varchar(100) not null,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

create table tblBankBranch(
	BankID	int foreign key references tblBank(BankID) not null,
	Branch varchar(100) not null,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
	primary key (BankID)
);
go

create table tblCheque(
	ChequeNo	varchar(100) primary key,
	BankID		int foreign key references tblBank(BankID),
	BranchID	int foreign key references tblBankBranch(BankID) default null,
	Amount		float,
	IssueDate	date,
	ReleaseDate	date,
	IsDeposite	bit,
	IsComplete  bit,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,	
);
go

create table tblBankAccount(
	AccountNo	varchar(100) primary key,
	PrintName	varchar(100),
	Bank		int foreign key references tblBank(BankID),
	Branch		int foreign key references tblBankBranch(BankID),
	CurrentAmount float,
	Interest	float,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,	
);

go
create table tblTransaction(
	TransactionID	int primary key identity(1,1),
	AccountNo	varchar(100) foreign key references tblBankAccount(AccountNo),
	DepositeAmount	float,
	WidthrowAmount	float,
	ChequeDeposit	float,
	ChequeWidthrow	float,
	Cheque			varchar(100) foreign key references tblCheque(ChequeNo) default null,
	TransactionType	varchar(20) check  (TransactionType in('Diposit','Widthrow')),
	PaymentMethod	varchar(20) check (PaymentMethod in('Cash','Cheque')),
	TransactionDate	datetime,	
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);
go

create table tblReadyCashAccount(
	AccountID int primary key identity(1,1),
	PrintName	varchar(100),
	CurrentAmount float,
	LedgerID	varchar(50),
	Comment ntext,
);
go

create table tblReadyCashTransaction(
	TransID int	primary key  identity(1,1),
	TransactionID	int foreign key references tblTransaction(TransactionID) default null,
	TrasactionDate	datetime,
	TrasactionType	varchar(20) check (TrasactionType in ('Deposit','Widthrow')),
	Amount	float,
	CreatedDate	datetime,
	Reason	ntext,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);
go

/* Sales Invoice */
create table tblSellingInvoiceHeader(
	InvoiceID	int	primary key,
	Date		date,
	Customer	int foreign key references tblCustomer(CustomerID),
	TotalAmount	float,
	PaymentDueDate	date,
	IsComplete	bit,
	CreatedDate	datetime,
	CreatedBy int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

go

create table tblSellingInvoiceItems(
	InvoiceID	int foreign key references tblSellingInvoiceHeader(InvoiceID),
	ItemID		int  foreign key references tblItem(ItemID),
	SellingPrice float,
	Discount	float,
	Quantity	float,
	SubTotal	float,
	CreatedDate	datetime,
	CreatedBy	int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);
go

create table tblVehicleRecord(
	TripID	int primary key	identity(1,1),
	VehicleNo	varchar(30) foreign key references tblVehicle(VehicleNo),
	MeterStart	float,
	MeterEnd	float,
	Distance	float,
	StartDate	datetime,
	EndDate		datetime,
	Driver		int	foreign key references tblEmployee(EmployeeID),
	Labour		varchar(50) default null,
	SalesRef	int foreign key references tblEmployee(EmployeeID),
	CreatedDate	datetime,
	CreatedBy	int foreign key references tblUser(UserID),
	ModifyDate	datetime default null,
	ModifyBy	int foreign key references tblUser(UserID) default null,
);

create table tblCategory(
	CatID int identity(1,1) primary key,
	Category varchar(100) not null,
	CreatedBy int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifyBy int foreign key references tblUser(UserID),
	ModifyDate datetime
);
go

create table tblSubCategory(
	SubCatID int identity(1,1) primary key,
	CatID int foreign key references tblCategory(CatID),
	SubCategory varchar(100),rrr
	CreatedBy int foreign key references tblUser(UserID),
	CreatedDate datetime,
	ModifyBy int foreign key references tblUser(UserID),
	ModifyDate datetime
);
go

ALTER TABLE [dbo].[tblItem]
ADD [SubCategory] int NULL 
GO

ALTER TABLE [dbo].[tblItem] 
ADD FOREIGN KEY ([SubCategory]) REFERENCES [tblSubCategory] ([SubCatID]) ON DELETE SET NULL ON UPDATE CASCADE
GO

